import matplotlib.pyplot as plt
import pymysql


# 分析吖咪直播的用户数据
def test(list_key, list_value, title):
    # 这两行代码解决 plt 中文显示的问题
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False

    plt.barh(list_key, list_value)  # 水平柱状图
    # plt.bar(list_key, list_value) # 垂直柱状图
    plt.title(title)
    plt.show()


def test1(list_key, list_value, title):
    # 这两行代码解决 plt 中文显示的问题
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False
    # 保证圆形
    plt.axes(aspect=1)
    plt.pie(x=list_value, labels=list_key, autopct='%3.1f %%')
    plt.title(title)
    plt.show()


def get_data_version():
    list_key = []
    list_value = []
    # 打开数据库连接，不指定数据库
    if isHome == 1:
        conn = pymysql.connect(host='localhost', user='root', password='XUHUAN.59')
    else:
        conn = pymysql.connect('localhost', 'root', 'xuhuan.5130638')
    conn.select_db('laimiao')
    cur = conn.cursor()  # 获取游标
    try:
        # print("===========================================================================================================")
        # print(jsonss)
        # 使用execute方法执行SQL语句
        for k in range(1, 10):
            cur.execute("select count(*) from userqq where sver='1.1." + str(k) + "' and islock=0")
            # 使用 fetchone() 方法获取一条数据
            data = cur.fetchone()
            # print('添加语句受影响的行数：', data[0])
            if data[0] > 100:
                list_key.append(str(k)+"("+str(data[0])+")")
                list_value.append(data[0])
    finally:
        try:
            cur.close()
            conn.close()
            print('sql执行成功')
        except Exception as e:
            print('sql执行成功异常：' + str(e))
    test(list_key, list_value, "用户版本分布占比")


def get_data_sex():
    list_key = []
    list_value = []
    # 打开数据库连接，不指定数据库
    if isHome == 1:
        conn = pymysql.connect(host='localhost', user='root', password='XUHUAN.59')
    else:
        conn = pymysql.connect('localhost', 'root', 'xuhuan.5130638')
    conn.select_db('laimiao')
    cur = conn.cursor()  # 获取游标
    try:
        # print("===========================================================================================================")
        # print(jsonss)
        # 使用execute方法执行SQL语句

        cur.execute("select count(*) from userqq where sex='男'")
        data = cur.fetchone()
        list_key.append("男("+str(data[0])+")")
        list_value.append(data[0])

        cur.execute("select count(*) from userqq where sex='女'")
        data1 = cur.fetchone()
        list_key.append("女("+str(data1[0])+")")
        list_value.append(data1[0])
    finally:
        try:
            cur.close()
            conn.close()
            print('sql执行成功')
        except Exception as e:
            print('sql执行成功异常：' + str(e))
    test1(list_key, list_value, "用户性别占比")


def get_data_share():
    list_key = []
    list_value = []
    # 打开数据库连接，不指定数据库
    if isHome == 1:
        conn = pymysql.connect(host='localhost', user='root', password='XUHUAN.59')
    else:
        conn = pymysql.connect('localhost', 'root', 'xuhuan.5130638')
    conn.select_db('laimiao')
    cur = conn.cursor()  # 获取游标
    try:
        # print("===========================================================================================================")
        # print(jsonss)
        # 使用execute方法执行SQL语句

        cur.execute("select count(*) from userqq where spreaduid='0'")
        data = cur.fetchone()
        list_key.append("自然注册("+str(data[0])+")")
        list_value.append(data[0])

        cur.execute("select count(*) from userqq")
        data1 = cur.fetchone()
        list_key.append("邀请注册("+str(data1[0]-data[0])+")")
        list_value.append(data1[0]-data[0])
    finally:
        try:
            cur.close()
            conn.close()
            print('sql执行成功')
        except Exception as e:
            print('sql执行成功异常：' + str(e))
    test1(list_key, list_value, "注册用户来源占比")


def get_data_sys():
    list_key = []
    list_value = []
    # 打开数据库连接，不指定数据库
    if isHome == 1:
        conn = pymysql.connect(host='localhost', user='root', password='XUHUAN.59')
    else:
        conn = pymysql.connect('localhost', 'root', 'xuhuan.5130638')
    conn.select_db('laimiao')
    cur = conn.cursor()  # 获取游标

    sysList = ["ios", "oppo", "vivo", "huawei", "xiaomi", "redmi", "honor", "realme"]
    try:
        # print("===========================================================================================================")
        # print(jsonss)
        # 使用execute方法执行SQL语句
        for item in sysList:
            cur.execute("select count(*) from userqq where plat LIKE '%"+item+"%'")
            data = cur.fetchone()
            list_key.append(item+"(" + str(data[0]) + ")")
            list_value.append(data[0])

        cur.execute("select count(*) from userqq")
        data1 = cur.fetchone()
        list_key.append("其他("+str(data1[0]-sum(list_value))+")")
        list_value.append(data1[0]-sum(list_value))
    finally:
        try:
            cur.close()
            conn.close()
            print('sql执行成功')
        except Exception as e:
            print('sql执行成功异常：' + str(e))
    test1(list_key, list_value, "用户手机版本占比")


isHome = 1  # 公司1 家2
if __name__ == '__main__':
    # 分析用户版本情况
    get_data_version()
    # 分析用户性别情况
    # get_data_sex()
    # 用户手机版本占比
    # get_data_sys()
    # 注册用户来源占比
    # get_data_share()

